/*
  FILENAME	: AppendInt-Rule.sql
  Copyright (C) 2013  Testmonger
  See https://maketestdata.codeplex.com/license

  NOTES		: 
    - param 1 number of iterations
	- param 2 is data element code or file id
	- param3 is target table, FileTemp default

	select * from [mtdRule]
    select * from FileTemp where rtrim(datacode) like 'FNAME'
	exec AppendInt 10,FNAME,FileTemp

	exec AppendInt 3,LNAME,FileTemp
	select * from filetemp where rtrim(datacode) like 'LNAME'
	select * from filetemp order by datacode desc, datachar desc
*/

IF OBJECT_ID(N'AppendInt') IS NOT NULL 
BEGIN
	DROP procedure AppendInt
END
GO

CREATE PROCEDURE AppendInt
	@p1 bigint, @p2 varchar(10), @p3 varchar(60)
AS
BEGIN
	DECLARE @cnt1 bigint, @cnt2 bigint, @cnt3 bigint, @deval varchar(60), @vsql nvarchar(1000),
		@acctnum varchar(20), @acctnum2 varchar(20), @deval2 varchar(60)

	-- create temp table of all values currently in filetemp
	select @vsql = 'select into '+@p3

	DECLARE @vtable TABLE (counterid int identity, acctnum varchar(20), datacode varchar(10), deval varchar(60))
	insert @vtable
	select distinct rtrim(acctnum), @p2, rtrim(datachar) from FileTemp
	where rtrim(datacode) like ''+@p2+''

	select @cnt1 = max(counterid), @cnt2 = 1 from @vtable

	--debug
	--select @cnt1,* from @vtable


WHILE @cnt1 > 0
BEGIN
	
	SELECT @deval = deval , @acctnum = acctnum
	from @vtable where counterid = (select max(counterid) from @vtable)

	--debug
	--SELECT @cnt1, @deval , @acctnum 	

	WHILE @cnt2 <= @p1
	BEGIN
		select @acctnum2 = @acctnum+cast(@cnt2 as varchar), 
			@deval2 = @deval+cast(@cnt2 as varchar)
		-- GET UNIQUE ACCT NUMS
		select @cnt3 = count(*) from filetemp where RTRIM(acctnum) like ''+@acctnum2+''
			and rtrim(datachar) like ''+@deval2+''
		IF @cnt3 = 0
		BEGIN
			select @vsql = 'insert into '+@p3+' values ('''+@acctnum2+''','''
			select @vsql = @vsql+@p2+''',NULL,'''+@deval2+''',NULL,NULL)'
			BEGIN
				EXEC sp_executesql @vsql 
			END
		END
			select @cnt2 = @cnt2 + 1
	END

	delete @vtable where counterid = (select max(counterid) from @vtable)
	select @cnt1 = isnull(max(counterid),0), @cnt2 = 1 from @vtable

--debug
--select  @cnt1, @cnt2 , @cnt3, @p1, @vsql, @acctnum2

END



END


/*
 exec AppendInt 10,LNAME,FileTemp
 select * from filetemp order by datachar
 delete filetemp where len(acctnum) > 4
 exec load_insub 'FileTemp','E:\APPS\InRoad\InTemplate\mytemplate1.csv'
run #		mm:ss
0	1		
1	11		0
2	111		3
3	1111	1:06
		
*/
